package com.crrcdt.meeting.utils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Objects;

/**
 * <p>
 * excel导入辅助工具类
 * </p>
 *
 * @author vinci
 * @since 2019-12-20
 */
public class ExcelImportUtil {
    private static Logger logger = LoggerFactory.getLogger(ExcelImportUtil.class);
    /**
     * 公式转换工具
     */
    private static HSSFFormulaEvaluator hssEvaluator;

    private static XSSFFormulaEvaluator xssEvaluator;

    /**
     * 初始化excel文件
     *
     * @param file the file
     * @return the Workbook
     * @throws IllegalArgumentException
     */
    public static Workbook init(MultipartFile file) {
        if (file == null) {
            throw new IllegalArgumentException("文件不存在！");
        }

        try (InputStream is = file.getInputStream()) {
            String fileName = file.getOriginalFilename();
            return initWbByFileSuffix(fileName, is);
        } catch (IOException e) {
            logger.error(e.getMessage());
            throw new IllegalArgumentException("文件初始化错误", e);
        }
    }

    /**
     * 初始化excel模板文件
     *
     * @param file the file
     * @return the Workbook
     * @throws IllegalArgumentException
     */
    public static Workbook initTemplateFile(File file) {
        if (file == null) {
            throw new IllegalArgumentException("文件不存在！");
        }
        try (InputStream is = new FileInputStream(file)) {
            String fileName = file.getName();
            return initWbByFileSuffix(fileName, is);
        } catch (IOException e) {
            logger.error(e.getMessage());
            throw new IllegalArgumentException("文件初始化错误", e);
        }
    }

    /**
     * 根据文件后缀名 初始化wb
     *
     * @param fileName 文件名称
     * @param is       the inputStream
     * @return the Workbook
     * @throws IOException the IOException
     */
    public static Workbook initWbByFileSuffix(String fileName, InputStream is) throws IOException {
        Workbook wb;
        if (fileName.toLowerCase().endsWith("xls")) {
            wb = new HSSFWorkbook(is);
            hssEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
        } else if (fileName.toLowerCase().endsWith("xlsx")) {
            wb = new XSSFWorkbook(is);
            xssEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
        } else {
            throw new IllegalArgumentException("文件类型不正确！");
        }
        return wb;
    }

    /**
     * excel类型转换String类型
     *
     * @param cell
     * @return
     */
    public static String convertCell2String(Cell cell) {
        if (cell == null) {
            return "";
        }
        String cellValue;
        switch (cell.getCellTypeEnum()) {
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    cellValue = removeDecimalPoint(String.valueOf(cell.getDateCellValue()));
                } else {
                    cellValue = removeDecimalPoint(String.valueOf(cell.getNumericCellValue()));
                }
                break;
            case FORMULA:
                if (hssEvaluator != null) {
                    HSSFCell hssfCell = (HSSFCell) cell;
                    CellValue evaluate = hssEvaluator.evaluate(hssfCell);
                    cellValue = formulaValue2String(evaluate);
                } else {
                    XSSFCell xssfCell = (XSSFCell) cell;
                    cellValue = xssEvaluator.evaluate(xssfCell).getStringValue();
                }
                break;
            case BLANK:
                cellValue = "";
                break;
            default:
                cellValue = "";
                break;
        }
        return cellValue;
    }

    /**
     * 根据公式转换后的类型进行转换成String
     *
     * @param evaluate
     * @return
     */
    private static String formulaValue2String(CellValue evaluate) {
        Objects.requireNonNull(evaluate);
        String cellValue;
        switch (evaluate.getCellTypeEnum()) {
            case STRING:
                cellValue = evaluate.getStringValue();
                break;
            case NUMERIC:
                cellValue = removeDecimalPoint(String.valueOf(evaluate.getNumberValue()));
                break;
            default:
                cellValue = evaluate.formatAsString();
                break;
        }
        return cellValue;
    }

    /**
     * 根据当前单元格行数和表单页判断是否是行合并单元格
     *
     * @param sheet
     * @param row
     * @return
     * @throws NullPointerException sheet为null
     */
    public static boolean isMergedCell(Sheet sheet, int row, int column) {
        Objects.requireNonNull(sheet, "表单不存在！");
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress rangeAddress = sheet.getMergedRegion(i);
            int firstRow = rangeAddress.getFirstRow();
            int lastRow = rangeAddress.getLastRow();
            int firstColumn = rangeAddress.getFirstColumn();
            int lastColumn = rangeAddress.getLastColumn();
            if (isRangeCell(row, column, firstRow, lastRow, firstColumn, lastColumn)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 获取单元格值 如果是合并单元格则进行处理
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     * @throws NullPointerException sheet为null
     */
    public static String getValueIfMerged(Sheet sheet, Row row, int rowLine, int column) {
        if (ExcelImportUtil.isMergedCell(sheet, rowLine, column)) {
            return ExcelImportUtil.getMergedValue(sheet, rowLine, column);
        } else {
            return ExcelImportUtil.convertCell2String(row.getCell(column));
        }
    }

    /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param row
     * @return
     * @throws NullPointerException 参数sheet为null
     */
    public static String getMergedValue(Sheet sheet, int row, int column) {
        Objects.requireNonNull(sheet, "表单不存在！");
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress rangeAddress = sheet.getMergedRegion(i);
            int firstColumn = rangeAddress.getFirstColumn();
            int lastColumn = rangeAddress.getLastColumn();
            int firstRow = rangeAddress.getFirstRow();
            int lastRow = rangeAddress.getLastRow();
            if (isRangeCell(row, column, firstRow, lastRow, firstColumn, lastColumn)) {
                Row fRow = sheet.getRow(firstRow);
                Cell fCell = fRow.getCell(firstColumn);
                return convertCell2String(fCell);
            }
        }
        return null;
    }

    /**
     * 判断是否在所属范围
     *
     * @param row
     * @param column
     * @param firstRow
     * @param lastRow
     * @param firstColumn
     * @param lastColumn
     * @return
     */
    private static boolean isRangeCell(int row, int column, int firstRow, int lastRow, int firstColumn, int lastColumn) {
        return row >= firstRow && row <= lastRow && column >= firstColumn && column <= lastColumn;
    }

    /**
     * excel字符串去除小数点后面多余的0
     *
     * @param str
     * @return
     */
    private static String removeDecimalPoint(String str) {
        if (str.contains(".")) {
            //去掉多余的0
            str = str.replaceAll("0+?$", "");
            //如最后一位是.则去掉
            str = str.replaceAll("[.]$", "");
        }
        return str;
    }

    /**
     * 新建颜色单元格格式
     *
     * @param workbook      工作簿
     * @param indexedColors 颜色类型
     * @return
     */
    public static CellStyle createColorStyle(Workbook workbook, IndexedColors indexedColors) {
        CellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.setFillForegroundColor(indexedColors.getIndex());
        newCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return newCellStyle;
    }

    /**
     * 创建单元格并设置格式
     *
     * @param row       行
     * @param column    列
     * @param cellValue 单元格值
     * @param cellStyle 单元格格式 若为null则不设置格式
     */
    public static void createCellWithStyle(Row row, int column, String cellValue, CellStyle cellStyle) {
        Cell startPositionCell = row.createCell(column);
        if (cellStyle != null) {
            startPositionCell.setCellStyle(cellStyle);
        }
        startPositionCell.setCellValue(cellValue);
    }

    /**
     * 创建单元格并复制
     *
     * @param row       行
     * @param column    列
     * @param cellValue 单元格值
     */
    public static void createBaseCell(Row row, int column, String cellValue) {
        Cell startPositionCell = row.createCell(column);
        startPositionCell.setCellValue(cellValue);
    }
}